package com.mcarrot.site.dao.impl;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.mcarrot.site.dao.AffairDAO;
import com.mcarrot.site.model.Affair;

public class AffairDAOImpl extends HibernateDaoSupport implements AffairDAO {
	
	private final Log log = LogFactory.getLog(getClass());

	@Override
	public Long saveOrUpdate(Affair affair) {
		try {
			getHibernateTemplate().saveOrUpdate(affair);
			return affair.getAffairId();
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error(": [del()] -----> ", e);
		}
		return OP_FAILURE;
	}

	@Override
	public Long del(Affair affair) {
		try {
			getHibernateTemplate().delete(affair);
			return OP_SUCCESSFUL;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error(": [del()] -----> ", e);
		}
		return OP_FAILURE;
	}

	@Override
	public Affair findById(Integer affairId) {
		Session session = this.getSession();
		Affair affair = (Affair) this.getHibernateTemplate().get(Affair.class, affairId);
		session.close();
		return affair;
	}

	@Override
	public List<Affair> listAll() {
		log.warn(" ----> listAll(): ");
		String hql = "from Affair";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listAllByKey(String key) {
		log.warn(" ----> listAllByKey(): ");
		String hql = "from Affair where title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listAllByStartDate(String key, String startDate) {
		log.warn(" ----> listAllByStartDate(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate > '" + startDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listAllByEndDate(String key, String endDate) {
		log.warn(" ----> listAllByEndDate(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate < '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listAllByConditions(String key, String startDate,
			String endDate) {
		log.warn(" ----> listAllByConditions(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate between '" + startDate + "' and '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> list(Integer start, Integer limit) {
		log.warn(" ----> list(): ");
		String hql = "from Affair";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}
	
	@Override
	public List<Affair> listByKey(String key, Integer start, Integer limit) {
		log.warn(" ----> listByKey(): ");
		String hql = "from Affair where title like '%" + key + "%'" +
				" or content like '%" + key + "%'" +
				" or description like '%" + key + "%'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}
	
	@Override
	public List<Affair> listByStartDate(String startDate, Integer start, Integer limit) {
		log.warn(" ----> listByStartDate(): ");
		String hql = "from Affair where subDate > '" + startDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listByEndDate(String endDate, Integer start, Integer limit) {
		log.warn(" ----> listByEndDate(): ");
		String hql = "from Affair where subDate < '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listByKeyAndStartDate(String key, String startDate,
			Integer start, Integer limit) {
		log.warn(" ----> listByKeyAndStartDate(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
				" or content like '%" + key + "%'" +
				" or description like '%" + key + "%')" +
				" and subDate > '" + startDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listByKeyAndEndDate(String key, String endDate,
			Integer start, Integer limit) {
		log.warn(" ----> listByKeyAndEndDate(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate < '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public List<Affair> listByConditions(String key, String startDate,
			String endDate, Integer start, Integer limit) {
		log.warn(" ----> listByConditions(): ");
		String hql = "from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate between '" + startDate + "' and '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		List<Affair> affairs = query.list();
		session.close();
		return affairs;
	}

	@Override
	public Integer getCount() {
		log.warn(" ----> getCount(): ");
		String hql = "select count(*) from Affair";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

	@Override
	public Integer getCountByKey(String key) {
		log.warn(" ----> getCountByKey(): ");
		String hql = "select count(*) from Affair where title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

	@Override
	public Integer getCountByKeyAndStartDate(String key, String startDate) {
		log.warn(" ----> getCountByKeyAndStartDate(): ");
		String hql = "select count(*) from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate > '" + startDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

	@Override
	public Integer getCountByKeyAndEndDate(String key, String endDate) {
		log.warn(" ----> getCountByKeyAndEndDate(): ");
		String hql = "select count(*) from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate < '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}
	
	@Override
	public Integer getCountByDate(String startDate, String endDate){
		log.warn(" ----> getCountByDate(): ");
		String hql = "select count(*) from Affair where subDate between '" + startDate + "' and '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}
	
	@Override
	public Integer getCountByConditions(String key, String startDate, String endDate) {
		log.warn(" ----> getCountByConditions(): ");
		String hql = "select count(*) from Affair where (title like '%" + key + "%'" +
			" or content like '%" + key + "%'" +
			" or description like '%" + key + "%')" +
			" and subDate between '" + startDate + "' and '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

	@Override
	public Integer getCountByStartDate(String startDate) {
		log.warn(" ----> getCountByStartDate(): ");
		String hql = "select count(*) from Affair where subDate > '" + startDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

	@Override
	public Integer getCountByEndDate(String endDate) {
		log.warn(" ----> getCountByEndDate(): ");
		String hql = "select count(*) from Affair where subDate < '" + endDate + "'";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		Integer count = ((Long)query.uniqueResult()).intValue();
		session.close();
		return count;
	}

}
